<!DOCTYPE html>



  


<html class="theme-next muse use-motion" lang="zh-CN">
<head><meta name="generator" content="Hexo 3.9.0">
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
<meta name="theme-color" content="#222">









<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">
















  
  
  <link href="/hexo/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css">







<link href="/hexo/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/hexo/css/main.css?v=5.1.4" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/hexo/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/hexo/images/favicon-32x32-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/hexo/images/favicon-16x16-next.png?v=5.1.4">


  <link rel="mask-icon" href="/hexo/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="开发笔记,">










<meta name="description" content="最近，公司的项目是一个接着一个，搞得大家伙是上气不接下气，实实在在体会到什么叫“等这个项目忙完了，终于可以收拾收拾准备忙下一个项目了”，这就是TMD惊喜！但就目前的大环境来说，能保持这种忙碌状态的公司，算是活得比较好的了吧。好了，闲话不多说了，来看今天想聊两个东西，都是和数据库相关的，也是和刚做的项目有关，也算是自己做一下总结吧。第一个，想聊一下存储过程。我这里呢，不展开聊概念了，就拿一个实际的例">
<meta name="keywords" content="开发笔记">
<meta property="og:type" content="article">
<meta property="og:title" content="从一次实践出发聊两个和数据库有关的点">
<meta property="og:url" content="https://tony_df.coding.net/2020/04/29/从一次实践出发聊两个和数据库有关的点/index.html">
<meta property="og:site_name" content="Tony&#39;s blog">
<meta property="og:description" content="最近，公司的项目是一个接着一个，搞得大家伙是上气不接下气，实实在在体会到什么叫“等这个项目忙完了，终于可以收拾收拾准备忙下一个项目了”，这就是TMD惊喜！但就目前的大环境来说，能保持这种忙碌状态的公司，算是活得比较好的了吧。好了，闲话不多说了，来看今天想聊两个东西，都是和数据库相关的，也是和刚做的项目有关，也算是自己做一下总结吧。第一个，想聊一下存储过程。我这里呢，不展开聊概念了，就拿一个实际的例">
<meta property="og:locale" content="zh-CN">
<meta property="og:updated_time" content="2020-04-29T23:55:47.532Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="从一次实践出发聊两个和数据库有关的点">
<meta name="twitter:description" content="最近，公司的项目是一个接着一个，搞得大家伙是上气不接下气，实实在在体会到什么叫“等这个项目忙完了，终于可以收拾收拾准备忙下一个项目了”，这就是TMD惊喜！但就目前的大环境来说，能保持这种忙碌状态的公司，算是活得比较好的了吧。好了，闲话不多说了，来看今天想聊两个东西，都是和数据库相关的，也是和刚做的项目有关，也算是自己做一下总结吧。第一个，想聊一下存储过程。我这里呢，不展开聊概念了，就拿一个实际的例">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/hexo/',
    scheme: 'Muse',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: 'Tony'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="https://tony_df.coding.net/2020/04/29/从一次实践出发聊两个和数据库有关的点/">





  <title>从一次实践出发聊两个和数据库有关的点 | Tony's blog</title>
  








</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-CN">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/hexo/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Tony's blog</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle">学着码代码,学着码人生;</p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/hexo/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-question-circle"></i> <br>
            
            Home
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/hexo/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-question-circle"></i> <br>
            
            Tags
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/hexo/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-question-circle"></i> <br>
            
            Archives
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="https://tony_df.coding.net/hexo/2020/04/29/从一次实践出发聊两个和数据库有关的点/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="Tony">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/hexo/images/avatar.gif">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Tony's blog">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">从一次实践出发聊两个和数据库有关的点</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">Posted on</span>
              
              <time title="Post created" itemprop="dateCreated datePublished" datetime="2020-04-29T21:26:25+08:00">
                2020-04-29
              </time>
            

            

            
          </span>

          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <p>最近，公司的项目是一个接着一个，搞得大家伙是上气不接下气，实实在在体会到什么叫“等这个项目忙完了，终于可以收拾收拾准备忙下一个项目了”，这就是TMD惊喜！<br>但就目前的大环境来说，能保持这种忙碌状态的公司，算是活得比较好的了吧。<br>好了，闲话不多说了，来看今天想聊两个东西，都是和数据库相关的，也是和刚做的项目有关，也算是自己做一下总结吧。<br>第一个，想聊一下存储过程。<br>我这里呢，不展开聊概念了，就拿一个实际的例子来简单聊一下，本来这个也没什么可聊的，只是因为在实际中踩了点坑，其实是基础知识不扎实，走了些弯路，总结下经验教训。<br>我遇到的业务需求其实很简单，就是获取一些数据列表，但难点在这个列表背后的数据关系，涉及到了6张表（别问为什么设计这么复杂，问我就要杀人了）！<br>在没有引入诸如es之类的中间件时，为了尽可能的提高检索效率，就写了个存储过程顶一下。<br>实际情况里，并不是每次检索都会走这6张表，有一个关键参数限制，当该参数为空值时，则会少关联两张表。<br>所以这个存储过程要满足的条件就是以下两点<br>1.可以动态拼接sql<br>2.能在执行时根据参数进行逻辑判断，执行不同的sql结果<br>基本就是这两点，再有就是业务上的东西<br>先来看下这个存储过程的整个代码（因为涉及内部业务，所以对检索字段等部分做了删减，不影响分析）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">USE</span> [cyscc_net]</span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SET</span> ANSI_NULLS <span class="keyword">ON</span></span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">SET</span> QUOTED_IDENTIFIER <span class="keyword">ON</span></span><br><span class="line"><span class="keyword">GO</span></span><br><span class="line"><span class="keyword">ALTER</span>  proc [dbo].[Proc_PageCourseInfo]</span><br><span class="line">@<span class="keyword">where</span> <span class="keyword">nvarchar</span>(<span class="number">300</span>),<span class="comment">--暂时提供一个where条件进来，后期改为字段的形式</span></span><br><span class="line">@pageindex <span class="built_in">int</span>,<span class="comment">--第几页</span></span><br><span class="line">@pagesize <span class="built_in">int</span>,<span class="comment">--一页多少条</span></span><br><span class="line">@<span class="keyword">order</span> <span class="keyword">nvarchar</span>(<span class="number">255</span>),</span><br><span class="line">@<span class="keyword">from</span> <span class="keyword">nvarchar</span>(<span class="number">255</span>)</span><br><span class="line"><span class="keyword">as</span></span><br><span class="line"><span class="keyword">set</span> nocount <span class="keyword">on</span>;  <span class="comment">--不返回计数，提高应用程序性能</span></span><br><span class="line"><span class="keyword">begin</span> <span class="comment">--开始</span></span><br><span class="line"><span class="keyword">declare</span> @pagebefore <span class="built_in">int</span>;<span class="comment">--起始页</span></span><br><span class="line"><span class="keyword">declare</span> @pagerear <span class="built_in">int</span>;<span class="comment">--结束页</span></span><br><span class="line"><span class="keyword">declare</span> @select_field <span class="keyword">nvarchar</span>(<span class="number">500</span>);</span><br><span class="line"><span class="keyword">declare</span> @order_field <span class="keyword">nvarchar</span>(<span class="number">255</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">set</span> @pagebefore=@pagesize*(@pageindex<span class="number">-1</span>);</span><br><span class="line"><span class="keyword">set</span> @pagerear=@pagebefore+@pagesize;</span><br><span class="line"><span class="keyword">set</span> @select_field=<span class="string">'id,name'</span>;<span class="comment">--（检索字段做了删减，实际业务里这里很凌乱~）</span></span><br><span class="line"><span class="keyword">set</span> @order_field=<span class="string">'created_at'</span>;</span><br><span class="line">if(@order&lt;&gt;'')</span><br><span class="line"><span class="keyword">set</span> @order_field= @order_field + <span class="string">','</span> + @<span class="keyword">order</span></span><br><span class="line"><span class="comment">--print @test</span></span><br><span class="line"><span class="comment">--创建一个虚拟表插入检索结果表数据</span></span><br><span class="line"><span class="keyword">declare</span> @params <span class="keyword">nvarchar</span>(<span class="number">2000</span>)</span><br><span class="line"><span class="keyword">declare</span> @<span class="keyword">sql</span> <span class="keyword">nvarchar</span>(<span class="number">2000</span>)</span><br><span class="line"></span><br><span class="line"><span class="keyword">set</span> @params=N<span class="string">'@pagebefore1 int,@pagerear1 int ,@from1 nvarchar(255)'</span>;</span><br><span class="line"><span class="keyword">set</span> @<span class="keyword">sql</span>=<span class="string">'</span></span><br><span class="line"><span class="string">declare @table table(</span></span><br><span class="line"><span class="string">    iid bigint identity, id uniqueidentifier, name nvarchar(300)</span></span><br><span class="line"><span class="string">)</span></span><br><span class="line"><span class="string">declare @table2 table(</span></span><br><span class="line"><span class="string">    iid bigint identity, id uniqueidentifier, name nvarchar(300)</span></span><br><span class="line"><span class="string">)</span></span><br><span class="line"><span class="string">insert @table</span></span><br><span class="line"><span class="string">select iid bigint identity, id uniqueidentifier, name nvarchar(300) from CourseInfo info,CourseSpeakers speaker,Course_Speaker cs ,</span></span><br><span class="line"><span class="string">(select *,row_number() over (partition by CourseID order by ProcessOrderNum desc) as RowNum from CourseProcess) process</span></span><br><span class="line"><span class="string">where info.CourseID=cs.CourseID and speaker.SpkID=cs.SpkID and process.CourseID=info.CourseID  and cs.order_num=1 and cs.SpkType=1 and '</span>+@<span class="keyword">where</span>+<span class="string">' order by '</span>+@order_field+<span class="string">'</span></span><br><span class="line"><span class="string">if @from1!=''''</span></span><br><span class="line"><span class="string">    insert @table2 select iid bigint identity, id uniqueidentifier, name nvarchar(300)</span></span><br><span class="line"><span class="string">    from @table where CourseID in</span></span><br><span class="line"><span class="string">    (select CourseID from Course_Location where LocationID in (select LocationID from Location where Abbreviation in (@from1)))</span></span><br><span class="line"><span class="string">if @from1=''''</span></span><br><span class="line"><span class="string">    select * from @table where  iid&gt;@pagebefore1 and iid&lt;=@pagerear1</span></span><br><span class="line"><span class="string">else</span></span><br><span class="line"><span class="string">    select * from @table2 where  iid&gt;@pagebefore1 and iid&lt;=@pagerear1</span></span><br><span class="line"><span class="string">if @from1=''''</span></span><br><span class="line"><span class="string">        select count(*) as rows from @table</span></span><br><span class="line"><span class="string">else</span></span><br><span class="line"><span class="string">    select count(*) as rows from @table2'</span></span><br><span class="line">exec sys.sp_executesql @<span class="keyword">sql</span>,@params,@pagebefore1=@pagebefore,@pagerear1=@pagerear,@from1=@<span class="keyword">from</span></span><br><span class="line"><span class="keyword">end</span>;<span class="comment">--结束</span></span><br></pre></td></tr></table></figure>

<p>删完了还剩这么多，但说实话，复杂的存储过程有的是，这个只是代码多点，但涉及到的知识点并不多<br>就直接聊坑吧<br>根据需求，要想在存储过程里动态拼接，必然要通过 exec或者exec sys.spexecutesql指令来执行拼接好的字符，为了方便，where条件是通过程序调用整个传入的，而没有在存储过程里设定过多的条件参数。<br>那这样做的问题就是需要拼接sql，而在存储过程里进行拼接的时候，还要在拼接的字符串里在传入参数，类似在代码里调用存储过程的时候传入参数那样。<br>这句话有点绕，但需要想明白，才能把这段脚本写好<br>先看一下代码里怎么调用存储过程</p>
<figure class="highlight c#"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> DataSet <span class="title">GetCourseInfoProc</span>(<span class="params">Common.PageModel model</span>)</span></span><br><span class="line"><span class="function"></span>&#123;</span><br><span class="line">    SqlParameter[] parameters = &#123;</span><br><span class="line">                <span class="keyword">new</span> SqlParameter(<span class="string">"@where"</span>, SqlDbType.NVarChar, <span class="number">300</span>),</span><br><span class="line">                <span class="keyword">new</span> SqlParameter(<span class="string">"@pageindex"</span>, SqlDbType.Int),</span><br><span class="line">                <span class="keyword">new</span> SqlParameter(<span class="string">"@pagesize"</span>, SqlDbType.Int),</span><br><span class="line">                <span class="keyword">new</span> SqlParameter(<span class="string">"@order"</span>, SqlDbType.NVarChar, <span class="number">255</span>),</span><br><span class="line">                <span class="keyword">new</span> SqlParameter(<span class="string">"@from"</span>,SqlDbType.NVarChar,<span class="number">255</span>)</span><br><span class="line">                &#125;;</span><br><span class="line">    parameters[<span class="number">0</span>].Value = model.<span class="keyword">where</span>;</span><br><span class="line">    parameters[<span class="number">1</span>].Value = model.pageIndex;</span><br><span class="line">    parameters[<span class="number">2</span>].Value = model.pageSize;</span><br><span class="line">    parameters[<span class="number">3</span>].Value = model.order_field;</span><br><span class="line">    parameters[<span class="number">4</span>].Value = model.<span class="keyword">from</span>;</span><br><span class="line">    <span class="keyword">return</span> DbHelperSQL.RunProcedure(<span class="string">"Proc_PageCourseInfo"</span>, parameters, <span class="string">"table"</span>);</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>这是很传统的写法，就是在程序里组织好要传入的参数，调用即可。<br>再看下上边的sql脚本，关键其实就在@sql这个变量里<br>@sql是个nvarchar类型的变量，注意，生成动态sql的时候，变量类型也是个坑，大段的sql语句就尽量用nvarchar或者ntext类型<br>在定义的这段字符串里，又declare了两个变量，table和table2，他们都是在@sql这个字符串内定义的，所以可以在@sql这个小世界里直接用<br>而除了在内部declare的变量外，还有两种类型的变量，<br>@where,@order_field是一类<br>@from1，@pagebefore1，@pagerear1是另一类<br>第一类就是在整个脚本开始时定义好的，在@sql中直接引用<br>第二类在@sql中使用，是作为形参数传入的，就像在程序里调用存储过程，他们就相当于上面程序里的@where,@pageindex等，而实参需要我们在@sql之外定义好，然后在执行时传入<br>如上面脚本里的@params,就是定义形参的过程<br>这一句</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">exec sys.sp_executesql @sql,@params,@pagebefore1=@pagebefore,@pagerear1=@pagerear,@from1=@from</span><br></pre></td></tr></table></figure>

<p>就是传入实参，获取结果。<br>注意在@sql这个小世界里，用@from1做逻辑判定的时候，判定条件为是否为空，写的时候时 if @from=’’’’，后边有4个引号，第一个和第三个做的时转义，二四才是实际的空值，好像还可以通过内置函数来完成。<br>另外，注意内含的if判断语句，如果包含的逻辑只执行一次sql语句，则不用谢begin，end否则是需要包在begin end里的，这特么也是个坑。<br>再有就是，拼接sql的话，不可避免的会带来sql注入的安全问题，所以在程序里调用之前，组织好的参数一定要做好过滤。<br>这个要说的大概就是这点，好像也没怎么说明白~希望能帮到遇到类似问题的同学。<br>另附一个参考链接：<a href="https://www.cnblogs.com/guohu/p/11142991.html" target="_blank" rel="noopener">https://www.cnblogs.com/guohu/p/11142991.html</a><br>这点就聊到这吧。</p>
<p>第二点要聊的也是个数据库，这个数据库做前端的同事可能很熟悉，叫indexedDB。<br>这个工具老早之前我就知道，但是，一直没用过，这次算是有一个机会。我之前常用页面缓存来处理一些逻辑，就是localstorage和sessionstorage这兄弟俩,一个永久存储（在不主动删的前提），一个会话存储，cookie用的也少了，我是觉得这俩兄弟几乎可以全场景替代cookie了。<br>这次我遇到的这个情况，用这一对兄弟也能解决，但处理起来总感觉姿势不太好，不够优雅，就想换一种方式，小小的折腾了一番，算是小有所得，但绝谈不上深入理解，所以此番介绍也只是冰山一角。<br>关于indexedDB的概念性知识，大家就自行百度吧，我这里提供两个indexedDB提供的API接口地址<a href="https://developer.mozilla.org/zh-CN/docs/Web/API/IndexedDB_API" target="_blank" rel="noopener">https://developer.mozilla.org/zh-CN/docs/Web/API/IndexedDB_API</a>和<a href="https://wangdoc.com/javascript/bom/indexeddb.html#indexeddb" target="_blank" rel="noopener">https://wangdoc.com/javascript/bom/indexeddb.html#indexeddb</a><br>之所以想用indexedDB,主要还是因为可以检索，因为只用缓存的话，想实现检索就得自己写函数方法，无非就是循环遍历之类，但既然js为我们提供了更好的解决办法，就试试吧。<br>indexedDB可以理解成类似redis那种的NoSql类型的数据库，现在市面上95%左右的浏览器都支持indexedDB，所以在项目中使用它完全没问题。<br>因为indexedDB提供的接口很多，我的需求就是最基础的存和取，来看下代码吧，<br>首先，针对常用到的接口，封装了一个简单的库，（这段代码有点多，不贴了，就是根据上面提到的两个地址中的一些教程做了一些简单的封装，分别封装了，写入，读取，删除，遍历，计数等方法）<br>然后把基本的接口封装好以后，就可以开始逻辑了<br>比如我在第一次加载页面的时候，需要先把数据准备好放到indexedDB中</p>
<figure class="highlight javascript"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">var</span> stores = <span class="string">'location'</span>;</span><br><span class="line"><span class="keyword">var</span> dbs = <span class="keyword">new</span> IndexedDb(<span class="string">'cyscc'</span>, &#123;</span><br><span class="line">    connUpgrade: <span class="function"><span class="keyword">function</span> (<span class="params">self, e</span>) </span>&#123;</span><br><span class="line">        self.createTable(stores, <span class="string">'LocationId'</span>, &#123;</span><br><span class="line">            success: <span class="function"><span class="keyword">function</span> (<span class="params">objectStore</span>) </span>&#123;</span><br><span class="line">                <span class="comment">//在Abbreviation字段上创建索引</span></span><br><span class="line">                objectStore.createIndex(<span class="string">'Abbreviation'</span>, <span class="string">'Abbreviation'</span>, &#123; <span class="attr">unique</span>: <span class="literal">false</span> &#125;);</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;)</span><br><span class="line">    &#125;,</span><br><span class="line">    connSuccess: <span class="function"><span class="keyword">function</span> (<span class="params">self, e</span>) </span>&#123;</span><br><span class="line">        dbs.count(stores, &#123;</span><br><span class="line">            success: <span class="function"><span class="keyword">function</span> (<span class="params">result</span>) </span>&#123;</span><br><span class="line">                <span class="keyword">if</span> (result == <span class="number">0</span>) &#123;</span><br><span class="line">                    $.getJSON(<span class="string">"/handlers/Index.ashx?action=locations"</span>, <span class="function"><span class="keyword">function</span> (<span class="params">json, status</span>) </span>&#123;</span><br><span class="line">                        <span class="keyword">if</span> (status == <span class="string">"success"</span> &amp;&amp; json.code == <span class="number">1</span>) &#123;</span><br><span class="line">                            chargeTpl(<span class="string">"location_tpl"</span>, <span class="string">"location_view"</span>, json.data.items, <span class="literal">false</span>);</span><br><span class="line">                            <span class="keyword">for</span> (<span class="keyword">var</span> i = <span class="number">0</span>; i &lt; json.data.items.length; i++) &#123;</span><br><span class="line">                                dbs.add(stores, json.data.items[i]);</span><br><span class="line">                            &#125;</span><br><span class="line">                            loadLocations();</span><br><span class="line">                        &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                            layer.alert(<span class="string">"网络有点堵，服务器开小差儿了，请稍后刷新页面重试"</span>, &#123; <span class="attr">icon</span>: <span class="number">5</span> &#125;, <span class="function"><span class="keyword">function</span> (<span class="params"></span>) </span>&#123;</span><br><span class="line">                                location.reload();</span><br><span class="line">                            &#125;);</span><br><span class="line">                        &#125;</span><br><span class="line">                    &#125;);</span><br><span class="line">                &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                    dbs.readAll(stores, &#123;</span><br><span class="line">                        success: <span class="function"><span class="keyword">function</span> (<span class="params">result</span>) </span>&#123;</span><br><span class="line">                            chargeTpl(<span class="string">"location_tpl"</span>, <span class="string">"location_view"</span>, result, <span class="literal">false</span>);</span><br><span class="line">                            loadLocations();</span><br><span class="line">                        &#125;</span><br><span class="line">                    &#125;)</span><br><span class="line">                &#125;</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;);</span><br><span class="line">    &#125;,</span><br><span class="line">    connError: <span class="function"><span class="keyword">function</span> (<span class="params">e</span>) </span>&#123;</span><br><span class="line">        <span class="built_in">console</span>.log(<span class="string">"数据加载失败"</span> + e);</span><br><span class="line">    &#125;</span><br><span class="line">&#125;, <span class="number">1</span>);</span><br><span class="line"></span><br><span class="line"><span class="function"><span class="keyword">function</span> <span class="title">loadLocations</span>(<span class="params">location_prefix</span>) </span>&#123;</span><br><span class="line">        <span class="comment">//先判定url参数，有参数的话，直接以url参数为准</span></span><br><span class="line">        <span class="keyword">var</span> query=getQueryString(<span class="string">"location"</span>);</span><br><span class="line">        <span class="keyword">if</span> (query) &#123;</span><br><span class="line">            location_prefix = query;</span><br><span class="line">            localStorage.setItem(<span class="string">"curr_location"</span>, query);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//如果没带过来这个参数，则从缓存里取</span></span><br><span class="line">        <span class="keyword">if</span> (!location_prefix) &#123;</span><br><span class="line">            location_prefix = localStorage.getItem(<span class="string">"curr_location"</span>);</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="comment">//在判定一下参数有没有取到值，取到的话开始逻辑</span></span><br><span class="line">        <span class="keyword">if</span> (location_prefix) &#123;</span><br><span class="line">            <span class="comment">//根据索引直接取值，顺滑~</span></span><br><span class="line">            dbs.getIndexValue(stores, <span class="string">'Abbreviation'</span>, location_prefix, <span class="literal">false</span>, &#123;</span><br><span class="line">                success: <span class="function"><span class="keyword">function</span> (<span class="params">locationInfo</span>) </span>&#123;</span><br><span class="line">                    <span class="keyword">if</span> (locationInfo) &#123;</span><br><span class="line">                        <span class="built_in">document</span>.title = locationInfo.Name + <span class="string">"创新学院"</span>;</span><br><span class="line">                        $(<span class="string">"#locationcss"</span>).attr(<span class="string">"href"</span>, <span class="string">"/public/css"</span> + location_prefix + <span class="string">".css"</span>);</span><br><span class="line">                        $(<span class="string">"#LocationPicker"</span>).html(<span class="string">"["</span> + locationInfo.Name + <span class="string">"]"</span>);</span><br><span class="line">                    &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">                        localStorage.removeItem(<span class="string">"curr_location"</span>);</span><br><span class="line">                        layer.msg(<span class="string">"该地域不存在"</span>, &#123; <span class="attr">icon</span>: <span class="number">0</span> &#125;);</span><br><span class="line">                    &#125;</span><br><span class="line">                &#125;</span><br><span class="line">            &#125;)</span><br><span class="line"></span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br></pre></td></tr></table></figure>

<p>在success的回调函数中，做好准备好数据后，就可以根据用户操作来完成一些其他操作,loadLocations方法里，有一个根据索引取值的方法，这一段通过indexedDB处理起来就很优雅，而如果通过缓存或者其他方式，或多或少在处理起来都会有些麻烦，可能代码量上会少，但终归达不到这种简单直接效果。<br>好了就这些吧，代码比较简单，不多解释了，关于IndexedDB,还有可以实现很多骚操作，这应该也算不上什么高阶引用，我觉得时挺基础的一个功能，只是用到的或者适合的应用场景比较少，但如果真的深入了解的话，其实是可以高效解决很多问题的。<br>虽然只是解决了一个简单的问题，但这次折腾呢，算是对indexedDB有了一个初步的认识，另外我想谈一点，在折腾的过程中，我多次想过放弃就用老办法弄就得了，因为用老的方法已经写完了，真的仅仅是觉得别扭，说好听点叫不够优雅，才一直坚持这用indexedDB把这段的逻辑重写了一遍。对我来说，我个人感觉这算是个小小的成长吧，因为在过程中我感受到了痛苦和挫折，并忍住了，哈哈<del>~</del>有点像讲大道理了，但差不多就是这样，之前看陈浩的直播，他提到，所谓成长是一定会感受到痛苦的。一帆风顺，长期呆在舒适区一定走不远，所以就是要刻意的去练习，去成长，才能走得更远。<br>好了，就这样吧。</p>

      
    </div>
    
    
    

    

    

    

    <footer class="post-footer">
      
        <div class="post-tags">
          
            <a href="/hexo/tags/开发笔记/" rel="tag"># 开发笔记</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/hexo/2020/04/02/分享一个处理视频文件的利器-2/" rel="next" title="分享一个处理视频文件的利器-2">
                <i class="fa fa-chevron-left"></i> 分享一个处理视频文件的利器-2
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/hexo/2020/05/21/薅bd的羊毛/" rel="prev" title="薅bd的羊毛?-1（上传文件到百度网盘）">
                薅bd的羊毛?-1（上传文件到百度网盘） <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
        <!-- Go to www.addthis.com/dashboard to customize your tools -->
<div class="addthis_inline_share_toolbox">
  <script type="text/javascript" src="//s7.addthis.com/js/300/addthis_widget.js#pubid=ra-5bbb2338b87602f0" async="async"></script>
</div>

      
    </div>
  </div>


          </div>
          


          

  
    <div class="comments" id="comments">
      <div id="lv-container" data-id="city" data-uid="MTAyMC8zOTA1My8xNTU4MA"></div>
    </div>

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      

      <section class="site-overview-wrap sidebar-panel sidebar-panel-active">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <p class="site-author-name" itemprop="name">Tony</p>
              <p class="site-description motion-element" itemprop="description">学习总结</p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/hexo/archives/">
              
                  <span class="site-state-item-count">58</span>
                  <span class="site-state-item-name">posts</span>
                </a>
              </div>
            

            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/hexo/tags/index.html">
                  <span class="site-state-item-count">7</span>
                  <span class="site-state-item-name">tags</span>
                </a>
              </div>
            

          </nav>

          

          

          
          

          
          

          

        </div>
      </section>

      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <div class="copyright">&copy; <span itemprop="copyrightYear">2021</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">Tony</span>

  
</div>


  <div class="powered-by">Powered by <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a></div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">Theme &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Muse</a> v5.1.4</div>




        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/hexo/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/hexo/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/hexo/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/hexo/js/src/motion.js?v=5.1.4"></script>



  
  

  
  <script type="text/javascript" src="/hexo/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/hexo/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/hexo/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  
    <script type="text/javascript">
      (function(d, s) {
        var j, e = d.getElementsByTagName(s)[0];
        if (typeof LivereTower === 'function') { return; }
        j = d.createElement(s);
        j.src = 'https://cdn-city.livere.com/js/embed.dist.js';
        j.async = true;
        e.parentNode.insertBefore(j, e);
      })(document, 'script');
    </script>
  












  





  

  

  

  
  

  

  

  

</body>
</html>
